Cross-Tab reports

 R&R enables you to select a group field and create totals for each value
 in that field. Cross-tabulating totals extend this capability by allowing
 you to create totals for two fields at the same group level. Since the
 resulting report resembles a table with totals tabulated down columns and
 across rows, it's referred to as a cross-tab report.

 For example, the following summary columnar report produces total sales
 for each product sold by the sales representative. In addition, it
 produces total sales for each product by all sales reps. But since sales
 reps are grouped within products, it can't produce the total sales of all
 products by each sales rep.

                        WEEKLY SALES SUMMARY REPORT
                        ===========================

 Product/Sales Rep                                      Amount
 -----------------                                      ------

 CIP
      Brenda                                              1200
      Ellen                                               1850
      Rob                                                 1425
      Roger                                               1025
                                                        ------
 CIP - All reps                                           5500

 dB Report Writer
      Brenda                                               450
      Ellen                                                675
      Rob                                                  305
      Roger                                                445
                                                        ------
 dB Report - All reps                                     1875

 R&R Report Writer
      Brenda                                              9820
      Ellen                                              11465
      Rob                                                 8775
      Roger                                              10450
                                                        ------
 R&R Report Writer - All reps                            40510

                                                        ======
 All Products - All sales reps                           47885

 Without using a cross-tab, you would have to create two separate reports
 to produce totals by product and by sales rep. A cross-tab report enables
 you to create both sets of totals on the same report. In addition, a
 cross-tab is more compact and thus easier to read.

                        WEEKLY SALES SUMMARY REPORT
                        ===========================

                  CIP      dB Report      R&R Report    All Products
                  ---      ---------      ----------    ------------

 Brenda          1200            450            9820           11470

 Ellen           1850            675           11465           13990

 Rob             1425            205            8775           10505

 Roger           1025            445           10450           11920

 All Sales Reps  5500           1875           40510           47885

 Note that in this example the crosstab computes sum totals. It is also
 possible to create a crosstab with other totals such as counts, minimums,
 and maximums. Use the following steps to create any crosstab report.

 1. SELECT A FIELD TO TOTAL
    In this example, you are summing the amount contained in the database
    field SALE_AMT. There is one record in the database for each product
    sold. Each record includes three fields: SALES_REP contains the name of
    the rep, PRODUCT contains the name of the product, and SALE_AMT
    contains the dollar amount.

 2. SELECT THE TYPE OF TOTAL
    R&R offers five types of totals: Sum, Count, Average, Highest
    (maximum), and Lowest (minimum).

 3. SELECT TWO FIELDS TO TABULATE
    In this example, you want to tabulate totals by product and by sales
    rep. These values are contained in the fields PRODUCT and SALES_REP.

 4. CREATE THE GROUP FIELD
    From the two fields you selected to tabulate in step 3, determine which
    one contains more values. In this example, there are four sales reps
    and only three products, so the group field should be SALES_REP.

    Use the /Sort-Group Sort-Fields command to enter SALES_REP as the level
    one sort-group field. Since sales reps will each be on a separate row,
    we will refer to SALES_REP as the ROW field, and PRODUCT as the COLUMN
    field.

 5. CREATE THE COLUMN ALLOCATION FIELDS
    For each value in the column field, you must create a calculated field.
    These fields enable you to allocate the sale amount to the correct
    product. You selected the field with the fewest values to be the column
    field to minimize the number of fields created in this step.

    A. This procedure applies to a crosstab of sums, averages, minimums,
       and maximums. For counts, refer to step 5B. Select /Field Calculate
       Create and enter the name CIP_Amount. Then enter the following
       expression:

       IIF(PRODUCT="CIP", SALE_AMT, 0)

       Think of this action as adding a field to the database that contains
       the amount only of the product is CIP, otherwise the field contains
       0. Follow this procedure again for the two other products to create
       the following fields:

       dBR_Amount = IIF(PRODUCT="dBR", SALE_AMT, 0)
       RnR_Amount - IIF(PRODUCT="R&R", SALE_AMT, 0)

    B. If you are producing a crosstab of counts, the calculated field
       expression will be slightly different. In this example, if you were
       counting the number of sales, the expressions would be as follows:

       CIP_Count = IIF(PRODUCT="CIP", 1, 0)
       dBR_Count = IIF(PRODUCT="dBR", 1, 0)
       RnR_Count = IIF(PRODUCT="R&R", 1, 0)

       Think of this expression as adding one to the count if the sale was
       for the product being counted. You would then complete steps six
       through ten, substituting these field names wherever you see the
       field names CIP_Amount, dBR_Amount, or RnR_Amount.

 6. CREATE THE CROSSTAB VALUES
    The fields you created in step 5 contain detail information for each
    sale. You must now create a total of each detail field, since the
    crosstab contains totals for each sales rep.

    Select /Field Total Create and enter the name CIP_X_Tab. To create the
    sales rep total for CIP, select Sum, CIP_Amount, and 1. The other two
    crosstab values are similarly calculated as follows:

    dBR_X_Tab = SALES_REP Sub-Sum of dBR_Amount
    RnR_X_Tab = SALES_REP Sub-Sum of RnR_Amount

 7. CREATE ROW TOTALS
    The total for each sales rep is calculated by simply adding up the
    three product totals.

    Select /Field Calculate Create and enter the name Rep_Total. Then enter
    the following expression.

    CIP_X_Tab + dBR_X_Tab + RnR_X_Tab

 8. CREATE COLUMN TOTALS
    Column totals are created the same way the crosstab values were in step
    6, except with the GRAND reset level.

    Select /Field Total Create and enter the name CIP_Total. To create the
    sales rep total for CIP, select Sum, CIP_Amount, and Grand. Follow this
    procedure again for the two other products to create the following
    fields:

    dBR_Total = Grand Sum of dBR_Amount
    RnR_Total = Grand Sum of RnR_Amount

 9. CREATE THE GRAND TOTAL
    The grand total in the lower right corner is both the total of the last
    column and the total of the last row. You will use a calculated field
    to define it as the addition of the three column totals.

    Select /Field Calculate Create and enter the name Grand_Tot. Then enter
    the following expression:

    CIP_Total + dBR_Total + RnR_Total

 10. CREATE THE REPORT FORMAT
    Now that all the necessary fields have been defined, create the
    following report format. Create the lines with the /Line Create command
    and insert fields with the F10 [Choices] key. Note that fields you will
    insert are contained in angle brackets in this example. This completes
    the crosstab.

 --------------------------------------------------------------------------
 Header                  WEEKLY SALES SUMMARY REPORT
 Header
 Header                   CIP       dB Report   R&R Report     All Products
 Header
 1SALE  <sales rep>  <CIP_X_Tab>  <dBR_X_Tab>  <RnR_X_Tab>     <Rep_Total>
 1SALE
 Summar  All Reps     <CIP_Total>  <dbR_Total>  <RnR_Total>     <Grand_Tot>
 --------------------------------------------------------------------------